package com.cari.oa.dao;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.cari.oa.domain.SoftModuleEntity;
import com.cari.oa.util.StrUtils;

@Repository
public class SoftModuleDao extends BaseDao<SoftModuleEntity> {
	
    /**
    * Title: getPage
    * Description: 分页查询
    * @author     hjx 
    * @date       2013-1-29 
    * @param name
    * @param pId
    * @param status
    * @param page
    * @param size
    * @return
    */
    @SuppressWarnings("unchecked")
	public List<SoftModuleEntity> getPage(String name, String pId, String status,String projectId, int page, int size) {
    	
		StringBuilder sql = new StringBuilder();
	    sql.append(" select * from (");
	    sql.append("    select  m.id,");      
	    sql.append("	        m.name,");   
	    sql.append("	        m.create_time as createTime,");                 
	    sql.append("            m.status,");             
	    sql.append("            m.description,");             
	    sql.append("            m.project_id as projectId,");               
	    sql.append("            prior m.id pId,");                                    //上级父节点Id
	    sql.append("            prior m.name pName,");                                //上级父节点名称
	    sql.append("            decode(connect_by_isleaf, 1, 'yes', 'no') isChild");  //是否是子节点
	    sql.append("    from  T_SOFTMODULE m");
	  	sql.append("    start with m.pId is null");                                //从parent为空开始扫描 
	    sql.append("    connect by prior m.id = m.pid");                           //以id为父列连接parent  
	    sql.append("    order siblings by m.id desc");                             //对层次排序  
	  	sql.append(" ) mo where 1=1 ");
        if(StrUtils.isNotEmpty(name)) {
        	sql.append("and mo.name like '%"+name+"%'");
           
        }
        if(StrUtils.isNotEmpty(pId)) {
        	sql.append("and mo.pId  like '%"+pId+"%'");
        }
        if(StrUtils.isNotEmpty(status)) {
        	sql.append("and mo.status = '"+ status+"'");
        }
        if(page == 0 && size == 0){
        	sql.append(" and mo.isChild ='no'");
        }
        if(StrUtils.isNotEmpty(projectId)) {
        	sql.append("and mo.projectId = '"+ projectId+"'");
        }
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.addScalar("id");
        query.addScalar("name");
        query.addScalar("createTime");
        query.addScalar("status");
        query.addScalar("description");
        query.addScalar("pId");
        query.addScalar("pName");
        query.addScalar("isChild");
        query.setResultTransformer(Transformers.aliasToBean(SoftModuleEntity.class));
        List<SoftModuleEntity> childList = new ArrayList<SoftModuleEntity>();
        if(page == 0 && size == 0){
        	childList = query.list(); 
        }else{
        	childList = query.setFirstResult((page - 1) * size).setMaxResults(size).list();
        }
		return childList;
    }
    
    /**
    * Title: getCount
    * Description:查询的记录数
    * @author    hjx 
    * @date       2013-1-29 
    * @param name
    * @param pId
    * @param status
    * @return
    */
    public long getCount(String name, String pId, String status,String projectId) {
    	List<Object> params = new ArrayList<Object>();
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select count(*) from SoftModuleEntity m where 1=1 ");
        if(StrUtils.isNotEmpty(name)) {
            stringBuilder.append("and m.name like ? ");
            params.add("%" + name + "%");
        }
        if(StrUtils.isNotEmpty(pId)) {
            stringBuilder.append("and m.pId like ? ");
            params.add("%" + pId + "%");
        }
        if(StrUtils.isNotEmpty(status)) {
            stringBuilder.append("and m.status = ? ");
            params.add(status);
        }
        if(StrUtils.isNotEmpty(projectId)) {
            stringBuilder.append("and m.projectId = ? ");
            params.add(projectId);
        }
        Query query = getSession().createQuery(stringBuilder.toString());
        for (int i = 0; i < params.size(); i++) {
            query.setParameter(i, params.get(i));
        }
        return (Long)query.uniqueResult();
    }

    /**
    * Title: existByEnname
    * Description:判断模块名称是否存在
    * @author    hjx 
    * @date       2013-1-29 
    * @param name
    * @return
    */
	public boolean existByEnname(String name , String projectId) {
		 Query query = getSession().createQuery("select count(*) from SoftModuleEntity where name = :name and projectId = :projectId");
         query.setString("name", name);
         query.setString("projectId", projectId);
         Long count = (Long)query.uniqueResult();
         return count > 0;
	}

	/**
	* Title: getModuleByProjectId
	* Description:
	* @author    hjx 
	* @date       2013-3-6 
	* @param projectId
	*/
	@SuppressWarnings("unchecked")
	public List<SoftModuleEntity> getModuleByProjectId(String projectId) {
		 Query query = getSession().createQuery("from SoftModuleEntity where projectId = :projectId");
         query.setString("projectId", projectId);
		 return query.list();
	}

	@SuppressWarnings("unchecked")
	public List<String> getByPId(String id) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select * from (");
		sql.append("     select    d.id as id");
		sql.append("     from  t_softmodule d");
		sql.append("     start with d.pid ='"+id+"'");
		sql.append("     connect by prior d.id = d.pid )");
	    SQLQuery query = getSession().createSQLQuery(sql.toString());
	    query.addScalar("id");
		List<String> list = query.list();
	    return list;
	}
}
